﻿using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Text;
using ImageManager.MyType;
using ImageManager.Model;

namespace ImageManager.DBAccess
{
    public class DataAccessUtils
    {
        internal static string Fix4SafeSql(object obj)
        {
            if (obj == null) return "";
            else return obj.ToString()!.Replace("'", "''");
        }
        internal static bool IsColumnName4Sql(string sql)
        {
            return Regex.IsMatch(sql, "^[a-zA-Z][a-zA-Z0-9_]*$");

        }
        internal static string GetTableName<T>()
        {
            foreach (var item in typeof(T).GetCustomAttributes(true))
            {
                var t = item.GetType();
                if (t == typeof(MyTableNameAttribute))
                {
                    return t.GetProperty(nameof(MyTableNameAttribute.TableName))!.GetValue(item)!.ToString()!;
                }
            }
            return typeof(T).Name;
        }
        internal static IEnumerable<PropertyInfo> GetChange<T>(T d1, T dt2) where T : BaseModel
        {
            var list = new List<PropertyInfo>();
            foreach (var p in typeof(T).GetProperties())
            {
                if (p.GetCustomAttribute<MyIgnoreColumnAttribute>() != null) continue;
                if (p.GetCustomAttribute<MyIgnoreCompareColumnAttribute>() != null) continue;
                var v1 = p.GetValue(d1);
                var v2 = p.GetValue(dt2);
                if (v1 == null && v2 == null) continue;
                if (v1 == null)
                {
                    list.Add(p);
                    continue;
                }
                if (!v1.Equals(v2))
                    list.Add(p);
            }
            return list;
        }
        internal static List<PropValueGetter> GetGetters<T>()
        {
            List<PropValueGetter> result = new List<PropValueGetter>();
            var props = typeof(T).GetProperties();
            foreach (var p in props)
            {
                var p_attrs = p.GetCustomAttributes(true);
                var ignore = false;
                foreach (var p_attr in p_attrs)
                {
                    if (p_attr.GetType() == typeof(MyIgnoreColumnAttribute))
                    {
                        ignore = true;
                        break;
                    }
                }
                if (!ignore)
                {
                    var r = new PropValueGetter(p);
                    result.Add(r);
                }
            }
            return result;
        }
        internal static List<PropValueSetter> GetSetters<T>()
        {
            return GetSetters(typeof(T));
        }
        internal static List<PropValueSetter> GetSetters(Type type)
        {
            List<PropValueSetter> result = new List<PropValueSetter>();
            var props = type.GetProperties();
            foreach (var p in props)
            {
                var p_attrs = p.GetCustomAttributes(true);
                var ignore = false;
                foreach (var p_attr in p_attrs)
                {
                    if (p_attr.GetType() == typeof(MyIgnoreColumnAttribute))
                    {
                        ignore = true;
                        break;
                    }
                }
                if (!ignore)
                {
                    var r = new PropValueSetter(p);
                    result.Add(r);
                }
            }
            return result;
        }

        internal static IDbCommand GetInsertSql<T>(T data, DbInstance dbInstance)
            where T : BaseModel, new()
        {
            var tableName = GetTableName<T>();
            var PropGetters = GetGetters<T>();
            StringBuilder sql = new StringBuilder("INSERT INTO ");
            sql.AppendFormat("\"{0}\"", tableName);
            StringBuilder tableColumnPart = new StringBuilder();
            StringBuilder valuePart = new StringBuilder();
            Dictionary<string, object?> paramList = new Dictionary<string, object?>();
            foreach (var p in PropGetters)
            {
                tableColumnPart.AppendFormat("{0},", p.PropName);
                valuePart.AppendFormat("@{0},", p.PropName);
                paramList.Add("@" + p.PropName, p.Get(data));
            }
            sql.AppendFormat("({0})", tableColumnPart.Remove(tableColumnPart.Length - 1, 1));
            sql.AppendFormat(" VALUES({0})", valuePart.Remove(valuePart.Length - 1, 1));
            return dbInstance.MakeQueryCommand(sql.ToString(), paramList.ToArray()!);
        }
        internal static IDbCommand GetUpdateSql<T>(T data, IEnumerable<PropertyInfo> changed, DbInstance dbInstance)
            where T : BaseModel, new()
        {
            var tableName = GetTableName<T>();
            var sql = new StringBuilder("UPDATE ");
            sql.AppendFormat("\"{0}\" SET ", tableName);
            Dictionary<string, object?> p = new Dictionary<string, object?>();
            foreach (var c in changed)
            {
                sql.AppendFormat("{0}=@{0},", c.Name);
                p.Add($"@{c.Name}", c.GetValue(data));
            }
            sql.Append("MODIFYDATE=@MODIFYDATE,FLAG=FLAG+1,MODIFIER=@MODIFIER WHERE PK = @PK AND DEL = 'N' AND FLAG+1 = @FLAG");
            p.Add("@MODIFYDATE", data.ModifyDate);
            p.Add("@MODIFIER", data.Modifier);
            p.Add("@PK", data.Pk);
            p.Add("@FLAG", data.Flag);
            return dbInstance.MakeQueryCommand(sql.ToString(), p.ToArray()!);
        }

        internal static IDbCommand GetDeleteSql<T>(T data, DbInstance dbInstance)
            where T : BaseModel, new()
        {
            var tableName = GetTableName<T>();
            var sql = new StringBuilder("UPDATE ");
            sql.AppendFormat("\"{0}\" SET ", tableName);
            Dictionary<string, object> p = new Dictionary<string, object>();
            sql.AppendFormat("DEL='Y',DELETEDATE=@DELETEDATE,DELETEUSER=@DELETEUSER WHERE PK = '{0}'", data.Pk);
            p.Add("@DELETEDATE", data.DeleteDate);
            p.Add("@DELETEUSER", data.DeleteUser);
            return dbInstance.MakeQueryCommand(sql.ToString(), p.ToArray()!);
        }
        internal static object? GetQueryVal(object? val)
        {
            //如果为枚举，在填入SQL参数时填入的是枚举值本体，而不是整数类型
            //这里将其转换成正数类型
            if (val != null && val.GetType().IsEnum)
                val = (int)val;
            //需要明确转为string类型，否则会以自定义类型传值，报类型转换异常
            if (val != null && val.GetType().GetCustomAttribute<MyTypeAttribute>() != null)
                val = val.ToString();
            return val;
        }

        public static DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
            }
            catch
            {
                dbt = DbType.Object;
            }
            return dbt;
        }
    }


}
